﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MINERP.DAL.Statistics
{
    public class ShopNoPayDAL
    {
        //private string sqlCon = ConfigurationManager.ConnectionStrings["fffff"].ToString();
        private string sqlCon = DALBase.ConnStr;
        private SqlConnection con;

        #region 商户列表
        /// <summary>
        /// 商户列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetShopList()
        {
            con = new SqlConnection(sqlCon);
            try
            {
                string sql = "select ShopId,ShopName,Area from shop";
                DataTable dt = new DataTable();
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 商户欠款统计
        /// <summary>
        /// 商户欠款统计
        /// </summary>
        public DataTable GetShopNoPay(List<int> shopIds, int startYear, int endYear, int startMonth, int endMonth, int noPayMoneyStatus)
        {
            con = new SqlConnection(sqlCon);
            try
            {
                string shopIdStr = "";
                foreach (int shopId in shopIds)
                {
                    shopIdStr += "," + shopId.ToString();
                }
                shopIdStr = shopIdStr.Substring(1);
                string sql = "select shopbill.MonthNoPay,shopbill.ShopId,shop.ShopName from";//,shopbill.MonthArea,shopbill.Year,shopbill.Month";
                sql += " (select sum(bill_detail_detail.count) as MonthNoPay,shopid";//sum(area) as MonthArea";//,datepart(yyyy,createtime) as Year,datepart(mm,createtime) as Month 
                sql+=" from shop_bill ";//,datepart(yyyy,createtime),datepart(mm,createtime) ";
                sql += " inner join bill_detail on shop_bill.billid=bill_detail.billId and datepart(yyyy,createtime)>=" + startYear + " and datepart(yyyy,createtime)<=" + endYear + " and datepart(mm,createtime)>=" + startMonth + " and datepart(mm,createtime)<=" + endMonth;
                sql += " inner join bill_detail_detail on bill_detail.billdetailid=bill_detail_detail.billdetailid";
                sql += " Group by shopid,Status";
                sql += " having shopid in (" + shopIdStr + ") and Status=" + noPayMoneyStatus + "";
                //if (dateEnable)                 //时间范围作为条件有效
                //{
                    //sql += " and max(datepart(yyyy,createtime))>=" + startYear + " and max(datepart(yyyy,createtime))<=" + endYear + " and max(datepart(mm,createtime))>=" + startMonth + " and max(datepart(mm,createtime))<=" + endMonth;
                //}
                    sql += ") as shopbill inner join shop on shopbill.shopid=shop.shopid order by shop.ShopName";//,shopbill.Year,shopbill.Month";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);

                dt.Columns.Add("MonthArea", typeof(double));
                string sql3 = "select sum(area) from bill_detail_detail,bill_detail,shop_bill where bill_detail_detail.type=1 and bill_detail.billdetailid=bill_detail_detail.billdetailid";
                sql3 += " and shop_bill.billid=bill_detail.billid and shop_bill.Status=" + noPayMoneyStatus;
                sql3 += " and datepart(mm,createtime)>=" + startMonth + " and datepart(mm,createtime)<=" + endMonth;
                sql3 += " and datepart(yyyy,createtime)>=" + startYear + " and datepart(yyyy,createtime)<=" + endYear + " and shop_bill.shopid=";
                string sql4 = "";
                foreach (DataRow r in dt.Rows)
                {
                    int shopId = Convert.ToInt32(r["ShopId"]);
                    sql4 = sql3 + shopId;
                    cmd.CommandText = sql4;
                    if (cmd.ExecuteScalar() != DBNull.Value)
                        r["MonthArea"] = Convert.ToDouble(cmd.ExecuteScalar());
                    else
                        r["MonthArea"] = 0;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public DataTable GetShopNoPay(List<int> shopIds, DateTime startTime,DateTime endTime, int noPayMoneyStatus)
        {
            con = new SqlConnection(sqlCon);
            try
            {
                string shopIdStr = "";
                foreach (int shopId in shopIds)
                {
                    shopIdStr += "," + shopId.ToString();
                }
                shopIdStr = shopIdStr.Substring(1);
                string sql = "select shopbill.MonthNoPay,shopbill.ShopId,shop.ShopName from";//,shopbill.MonthArea,shopbill.Year,shopbill.Month";
                sql += " (select sum(bill_detail_detail.count) as MonthNoPay,shopid";//sum(area) as MonthArea";//,datepart(yyyy,createtime) as Year,datepart(mm,createtime) as Month 
                sql += " from shop_bill ";//,datepart(yyyy,createtime),datepart(mm,createtime) ";
                sql += " inner join bill_detail on shop_bill.billid=bill_detail.billId and createtime>='" + startTime + "' and createtime<='" + endTime + "'";
                sql += " inner join bill_detail_detail on bill_detail.billdetailid=bill_detail_detail.billdetailid";
                sql += " Group by shopid,Status";
                sql += " having shopid in (" + shopIdStr + ") and Status=" + noPayMoneyStatus + "";
                //if (dateEnable)                 //时间范围作为条件有效
                //{
                //sql += " and max(datepart(yyyy,createtime))>=" + startYear + " and max(datepart(yyyy,createtime))<=" + endYear + " and max(datepart(mm,createtime))>=" + startMonth + " and max(datepart(mm,createtime))<=" + endMonth;
                //}
                sql += ") as shopbill inner join shop on shopbill.shopid=shop.shopid order by shop.ShopName";//,shopbill.Year,shopbill.Month";
                SqlCommand cmd = new SqlCommand(sql, con);
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                con.Open();
                adapter.Fill(dt);

                dt.Columns.Add("MonthArea", typeof(double));
                string sql3 = "select sum(area) from bill_detail_detail,bill_detail,shop_bill where bill_detail_detail.type=1 and bill_detail.billdetailid=bill_detail_detail.billdetailid";
                sql3 += " and shop_bill.billid=bill_detail.billid and shop_bill.Status=" + noPayMoneyStatus;
                sql3 += " and createtime>='" + startTime + "' and createtime<='" + endTime + "'  and shop_bill.shopid=";               
                string sql4 = "";
                foreach (DataRow r in dt.Rows)
                {
                    int shopId = Convert.ToInt32(r["ShopId"]);
                    sql4 = sql3 + shopId;
                    cmd.CommandText = sql4;
                    if (cmd.ExecuteScalar() != DBNull.Value)
                        r["MonthArea"] = Convert.ToDouble(cmd.ExecuteScalar());
                    else
                        r["MonthArea"] = 0;
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

    }
}
